﻿using CodeAngel.Domain;
using CodeAngel.Domain.Aggregates;
using CodeAngel.Domain.Framework;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace CodeAngel.Services
{
    public static class Extensions
    {
        /// <summary>
        /// Saves changes to the object graph managed by this aggregate to the database.
        /// </summary>
        public static DtoHost SaveToDatabase(this Aggregate thisAggregate, DtoHost dtoHost, string userName, string password)
        {

            thisAggregate.User = GetUser(userName, password);

            // Build the object graph and verify it is in a state where it can be saved.
            thisAggregate.BuildObjectGraph(dtoHost);
            if (!thisAggregate.CanSave()) throw new SaveException(thisAggregate.Root.GetInvalidRules().ToViolationString());

            SqlTransaction ThisTransaction = null;
            using (var ThisConnection = new SqlConnection(GetConnectionString()))
            {
                try
                {
                    ThisConnection.Open();
                    ThisTransaction = ThisConnection.BeginTransaction();
                    foreach (var TheseDtos in thisAggregate.GetUpdateableDtos())
                    {
                        foreach (IDto ThisDto in TheseDtos)
                        {
                            if (ThisDto.State == DtoChangedState.New || ThisDto.State == DtoChangedState.Modified)
                                ThisDto.SaveDtoToDatabase(ThisConnection, ThisTransaction, userName, password);

                            else if (ThisDto.State == DtoChangedState.Deleted)
                            {
                                ThisDto.DeleteDtoFromDatabase(ThisConnection, ThisTransaction, userName, password);
                                dtoHost.RemoveDto(ThisDto);
                            }
                        }
                    }

                    ThisTransaction.Commit();
                    return dtoHost;
                }
                catch (Exception ex)
                {
                    if (ThisTransaction != null) ThisTransaction.Rollback();
                    throw ex;
                }
                finally
                {
                    if (ThisConnection != null && ThisConnection.State != ConnectionState.Closed) ThisConnection.Close();
                }
            }

        }

        /// <summary>
        /// Deletes the identified entity from the database using the identified DeleteAggregate.
        /// </summary>
        public static void DeleteFromDatabase(this DeleteAggregate thisAggregate, Guid id, string userName, string password)
        {
            thisAggregate.User = GetUser(userName, password);
            if (!thisAggregate.UserHasPermissionToDelete())
                throw new Exception("Current user does not have the necessary permissions to delete the data in this aggregate: " + thisAggregate.GetType().Name);

            SqlTransaction ThisTransaction = null;
            using (var ThisConnection = new SqlConnection(GetConnectionString()))
            {
                try
                {
                    ThisConnection.Open();
                    ThisTransaction = ThisConnection.BeginTransaction();

                    // TODO: add support to delete multiple child entities... see Aggregate.SaveOnServer

                    var ThisDto = thisAggregate.GetDto(id);
                    ThisDto.DeleteDtoFromDatabase(ThisConnection, ThisTransaction, userName, password);
                    ThisTransaction.Commit();
                }
                catch (Exception ex)
                {
                    if (ThisTransaction != null) ThisTransaction.Rollback();
                    throw ex;
                }
                finally
                {
                    if (ThisConnection != null && ThisConnection.State != ConnectionState.Closed) ThisConnection.Close();
                }
            }
        }

        /// <summary>
        /// Gets the connection string to the database.
        /// </summary>
        private static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["DomainDatabaseConnection"].ConnectionString;
        }

        /// <summary>
        /// Gets the User object based on the identified credentials.
        /// </summary>
        private static User GetUser(string userName, string password)
        {
            var UserLogInAggregate = new UserLogInAggregate();
            string Filter = string.Format("SELECT UserID FROM [User] WHERE UserName = '{0}' AND Password = '{1}'", userName, password);
            var dtoHost = UserLogInAggregate.GetDtoHost(Filter, userName, password);
            UserLogInAggregate.BuildObjectGraph(dtoHost);
            return UserLogInAggregate.Root;
        }

        /// <summary>
        /// Saves the identified DTO to the database.  If the DTO is new, it will be added; if it
        /// already exists, it will be updated.
        /// </summary>
        private static void SaveDtoToDatabase(this IDto thisDto, SqlConnection thisConnection, SqlTransaction thisTransaction, string userName, string password)
        {
            try
            {
                var SaveCommand = new SqlCommand();
                SaveCommand.Connection = thisConnection;
                SaveCommand.Transaction = thisTransaction;
                SaveCommand.CommandType = CommandType.StoredProcedure;
                SaveCommand.CommandText = thisDto.State == DtoChangedState.New ? thisDto.GetInsertStoredProcedureName() : thisDto.GetUpdateStoredProcedureName();
                thisDto.CreateSaveParameters(SaveCommand.Parameters);
                SaveCommand.Parameters.AddWithValue("@ThisUser", userName);
                SaveCommand.Parameters.AddWithValue("@UserPassword", password);
                SaveCommand.ExecuteNonQuery();

                thisDto.State = DtoChangedState.Unchanged;

                // The TimeStamp field is updated each time the row is updated.
                //thisDto.UpdateTimeStampField((byte[])SaveCommand.Parameters["@TimeStamp"].Value);
                thisDto.TimeStamp = (byte[])SaveCommand.Parameters["@TimeStamp"].Value;

            }
            catch (Exception ex)
            {
                throw new SaveDtoException(ex);
            }
        }

        /// <summary>
        /// Deletes the identified DTO from the database.
        /// </summary>
        private static void DeleteDtoFromDatabase(this IDto thisDto, SqlConnection thisConnection, SqlTransaction thisTransaction, string userName, string password)
        {
            try
            {
                var DeleteCommand = new SqlCommand();
                DeleteCommand.Connection = thisConnection;
                DeleteCommand.Transaction = thisTransaction;
                DeleteCommand.CommandType = CommandType.StoredProcedure;
                DeleteCommand.CommandText = thisDto.GetDeleteStoredProcedureName();
                thisDto.CreateDeleteParameters(DeleteCommand.Parameters);
                DeleteCommand.Parameters.AddWithValue("@ThisUser", userName);
                DeleteCommand.Parameters.AddWithValue("@UserPassword", password);
                DeleteCommand.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new DeleteDtoException(ex);
            }
        }

    }
}